package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.UserDao;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.JDBC;
import jdk.nashorn.internal.scripts.JD;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserImpl implements UserDao {


    /**
     * 去查询肯定是去数据库去查询，所以需要一个连接数据库的代码
     *
     * @param userModel
     * @return
     */
    @Override
    public UserModel findByUsernameAndPassword(UserModel userModel) {
        String sql = "select * from user  where username ='" + userModel.getUsername() + "' and password='" + userModel.getPassword() + "';";
//        ResultSet 结果集  set叫做集合
//        对于结果集我们需要将它封装到model
        ResultSet rs = JDBC.getRs(sql);
//  创建一个空的UserModel，主要用于service的返回值去判断是否数据库有值
        UserModel model = null;
//        model=rs解析后的数据
//        如果查询出来的条数不确定，但是我们又要对齐进行遍历，循环，
//        那么我们需要用到循环语句while
        try {
            while (rs.next()) {
                model = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        if (model != null) {
//            return new UserModel();
//        }
        return model;
    }

    @Override
    public List<UserModel> findAll(UserModel userModel) {
//        1.编写sql语句，
        String sql = "select *,date_format(birthday,'%Y-%m-%d') temp from user  where 1=1 ";
        sql += setSql(userModel);
        sql += " limit " + (userModel.getPage() + -1) * +userModel.getLimit() + ", " + userModel.getLimit();
//        2.得到结果集，
        ResultSet rs = JDBC.getRs(sql);
//        3.解析结果集


//        4.返回数据
        return getList(rs);
    }

    @Override
    public int getCount(UserModel userModel) {
        //        1.编写sql语句，
        String sql = "select count(*) count from user where 1=1 ";
        sql += setSql(userModel);
//        2.得到结果集，
        ResultSet rs = JDBC.getRs(sql);
//        3.解析结果集
        int count = 0;
        try {
            while (rs.next()) {
                count = rs.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        4.返回数据
        return count;
    }

    /**
     * 封装sql语句
     *
     * @param userModel
     * @return
     */
    private String setSql(UserModel userModel) {
        String sql = "";

        if (!"".equals(userModel.getUsername())) {
            sql += " and username like '%" + userModel.getUsername() + "%' ";
        }
        if (!"".equals(userModel.getPhone())) {
            sql += " and phone like '%" + userModel.getPhone() + "%' ";
        }
        if (!"".equals(userModel.getSex())) {
            sql += "and sex='" + userModel.getSex() + "'";
        }
//        生日
        if (!"".equals(userModel.getStart()) && "".equals(userModel.getEnd())) {
            sql += " and birthday >='" + userModel.getStart() + "' ";
        }
        if ("".equals(userModel.getStart()) && !"".equals(userModel.getEnd())) {
            sql += " and birthday <='" + userModel.getEnd() + "' ";
        }
        if (!"".equals(userModel.getStart()) && !"".equals(userModel.getEnd())) {
            sql += " and birthday between '" + userModel.getStart() + "' and '" + userModel.getEnd() + "'";
        }
//        状态
        if (userModel.getEnable() != -1) {
            sql += " and enable=" + userModel.getEnable();
        }
//        爱好
        if (!"".equals(userModel.getHobby())) {
            String[] hobbys = userModel.getHobby().split(",");
            sql += " and (";
            for (int i = 0; i < hobbys.length; i++) {
                if (i == 0) {
                    sql += "  find_in_set('" + hobbys[i] + "',hobby) ";
                } else {
                    sql += " or find_in_set('" + hobbys[i] + "',hobby) ";
                }

            }
            sql += " )";
        }
        sql += " order by create_time desc ";
        return sql;
    }

    @Override
    public int del(UserModel userModel) {
        String sql = "delete from user where id=" + userModel.getId() + ";";
        return JDBC.update(sql);
    }

    @Override
    public int add(UserModel userModel) {
        String sql = "INSERT INTO `user` (" +
                "`username`, `address`, `create_time`, " +
                "`email`, `enable`, `password`," +
                " `phone`, `sex`, `update_time`," +
                " `hobby`, `birthday`, `remark`) VALUES (" +
                "'" + userModel.getUsername() + "', '" + userModel.getAddress() + "', now()," +
                " '" + userModel.getEmail() + "', '" + userModel.getEnable() + "', '" + userModel.getPassword() + "'," +
                " '" + userModel.getPhone() + "', '" + userModel.getSex() + "', now(), " +
                "'" + userModel.getHobby() + "', '" + userModel.getBirthday() + "', '');\n";
        return JDBC.update(sql);
    }

    @Override
    public UserModel findById(UserModel userModel) {
        String sql = "select *,date_format(birthday,'%Y-%m-%d') temp from user where id=" + userModel.getId();
        //        2.得到结果集，
        ResultSet rs = JDBC.getRs(sql);
//        3.解析结果集
        return getList(rs).get(0);
    }

    //    在数据库里面去判断，如果数据库enable为1，则修改为0，否则修改为1
    public int enable(UserModel userModel) {
//        UserModel model = findById(userModel);

//        String sql = "update user set enable=" + (userModel.getEnable() == 1 ? 0 : 1) +
//                " where id=" + userModel.getId();
        String sql="update user set enable=\n" +
                " case\n" +
                " when 1 then 0\n" +
                " when 0 then 1\n" +
                "end" +
                " where id="+userModel.getId();
        return JDBC.update(sql);
    }

    @Override
    public int update(UserModel userModel) {
        String sql = "UPDATE `user` SET " +
                "`username` = '" + userModel.getUsername() + "'," +
                " `address` = '" + userModel.getAddress() + "', " +

                " `email` = '" + userModel.getEmail() + "', " +
                " `enable` = '" + userModel.getEnable() + "', ";
        if (userModel.getPassword() != null) {
            sql += " `password` = '" + userModel.getPassword() + "', ";
        }
        if (userModel.getPhone() != null) {
            sql += " `phone` = '" + userModel.getPhone() + "',";
        }
        sql += " `sex` = '" + userModel.getSex() + "'," +
                " `update_time` = now(), " +
                " `hobby` = '" + userModel.getHobby() + "', ";
        if (userModel.getBirthday() != null) {
            sql += " `birthday` = '" + userModel.getBirthday() + "', ";
        }


        sql += " remark='' WHERE (`id` = '" + userModel.getId() + "');";
        return JDBC.update(sql);
    }

    @Override
    public int updatePassword(UserModel userModel) {
        return 0;
    }

    private List<UserModel> getList(ResultSet rs) {
        List<UserModel> list = new ArrayList<>();
        try {
            while (rs.next()) {
//                注意：密码永远不要查询出来
                UserModel model = new UserModel();
                model.setId(rs.getInt("id"));
                model.setUsername(rs.getString("username"));
                model.setAddress(rs.getString("address"));
                model.setEmail(rs.getString("email"));
                model.setEnable(rs.getInt("enable"));
                model.setCreateTime(rs.getString("create_time"));
                model.setUpdateTime(rs.getString("update_time"));
                model.setHobby(rs.getString("hobby"));
                model.setBirthday(rs.getString("temp"));
                model.setSex(rs.getString("sex"));
                model.setPhone(rs.getString("phone"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        4.返回数据
        return list;
    }
}
